using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class WareHouseDAO : IWareHouseDAO
    {
        //query strings
        private const string SQL_SELECT_WAREHOUSE = "SELECT * FROM WareHouse";
        private const string SQL_SELECT_WAREHOUSE_BY_FACTORY_ID = "SELECT * FROM WareHouse WHERE FactoryID=@FactoryID";
        private const string SQL_INSERT_WAREHOUSE = "INSERT INTO WareHouse (FactoryID, WareHouseName, Location) VALUES (@FactoryID, @WareHouseName, @Location)";
        private const string SQL_UPDATE_WAREHOUSE = "UPDATE WareHouse SET FactoryID=@FactoryID, WareHouseName=@WareHouseName, Location=@Location WHERE WareHouseID=@WareHouseID";

        //parameters
        private const string PARAM_WAREHOUSE_ID = "@WareHouseID";
        private const string PARAM_FACTORY_ID = "@FactoryID";
        private const string PARAM_WAREHOUSE_NAME = "@WareHouseName";
        private const string PARAM_LOCATION = "@Location";

        #region IWareHouseDAO Members

        public IList<WareHouseInfo> GetAll()
        {
            IList<WareHouseInfo> warehouses = new List<WareHouseInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_WAREHOUSE, null))
            {
                while (reader.Read())
                {
                    WareHouseInfo wh = new WareHouseInfo();
                    wh.WarehouseID = reader.GetInt32(0);
                    wh.FactoryID = reader.GetInt32(1);
                    wh.WarehouseName = reader.GetString(2);
                    wh.Location = reader.GetString(3);
                    wh.Categories = new CategoryDAO().GetByWareHouseID(wh.WarehouseID.ToString());
                    warehouses.Add(wh);
                }
            }
            return warehouses;
        }

        public WareHouseInfo GetByFactoryID(string factoryID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_FACTORY_ID, SqlDbType.Int) };
            parms[0].Value = int.Parse(factoryID);

            WareHouseInfo wh = new WareHouseInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_WAREHOUSE_BY_FACTORY_ID, parms))
            {                                 
                while (reader.Read())
                {
                    
                    wh.WarehouseID = reader.GetInt32(0);
                    wh.FactoryID = reader.GetInt32(1);
                    wh.WarehouseName = reader.GetString(2);
                    wh.Location = reader.GetString(3);
                    wh.Categories = new CategoryDAO().GetByWareHouseID(wh.WarehouseID.ToString());
                }
            }
            return wh;
        }

        public bool Insert(WareHouseInfo wareHouse)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_FACTORY_ID, SqlDbType.Int),
                                            new SqlParameter(PARAM_WAREHOUSE_NAME, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_LOCATION, SqlDbType.NVarChar) };
            parms[0].Value = wareHouse.FactoryID;
            parms[1].Value = wareHouse.WarehouseName;
            parms[2].Value = wareHouse.Location;

            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_WAREHOUSE, parms);
                SQLDBHelper.SetNewIdentity(conn, wareHouse.WarehouseID);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;                
            }
            //return false;
        }

        public bool Update(WareHouseInfo wareHouse)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_FACTORY_ID, SqlDbType.Int),
                                            new SqlParameter(PARAM_WAREHOUSE_NAME, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_LOCATION, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_WAREHOUSE_ID, SqlDbType.Int) };
            parms[0].Value = wareHouse.FactoryID;
            parms[1].Value = wareHouse.WarehouseName;
            parms[2].Value = wareHouse.Location;
            parms[3].Value = wareHouse.WarehouseID; 

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_WAREHOUSE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;                
            }
            //return false;
        }

        #endregion
    }
}
